import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.Graphics;
import java.awt.GridBagLayout;
import java.awt.GridLayout;
import java.awt.Image;
import java.awt.Insets;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;
import java.sql.Connection;
import java.sql.DriverManager;

import javax.swing.BorderFactory;
import javax.swing.ButtonModel;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.ScrollPaneConstants;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;
import javax.swing.table.TableModel;


public class AdminFinancePanel extends JPanel{
	JPanel panel;
	String[] courseCols={"Section", "Title","Description","Amount"};
	Object[][] courseDatas={};
	String[] studentCols={"StudentID","GPA","Award","Amount"};
	Object[][] studentDatas={};
	
	Image img;
	static String te;
	static String tu;
	JTextField termtext=new JTextField();
	JTextField tuitiontext=new JTextField();
	JTable courseT,studentT;
	int srow,crow,pid,spid;
	boolean sb[][],cb[][];
	
	public AdminFinancePanel(){
		
		img=new ImageIcon("admin_finance_background.png").getImage();
		
		
		setBackground(Color.WHITE);
		panel=new JPanel();
		panel.setBackground(Color.WHITE);
		panel.setPreferredSize(new Dimension(600,400));
		panel.setLayout(new BorderLayout());
		setOpaque(false);
		panel.setOpaque(false);
		
		//north
		JPanel north=new JPanel();
		north.setOpaque(false);
		north.setLayout(new GridLayout(4,1));
		JPanel n1=new JPanel();
		n1.setOpaque(false);
		n1.setLayout(new GridLayout(1,4));
		JPanel n2=new JPanel();
		n2.setOpaque(false);
		n2.setLayout(new GridLayout(1,4));
		
		initialize();
		
		JLabel term=new JLabel("Term: ");
		JLabel tuitionRate=new JLabel("Tuition Rate: ");
		
		
		termtext.addKeyListener(new KeyListener() {
		      public void keyPressed(KeyEvent keyEvent) {
			       
		      }
		      public void keyReleased(KeyEvent keyEvent) {
		    	  te=termtext.getText();
			       System.out.println(te);
		      }
		      public void keyTyped(KeyEvent keyEvent) {
		      }
		    });
		tuitiontext.addKeyListener(new KeyListener() {
		      public void keyPressed(KeyEvent keyEvent) {
			       
		      }
		      public void keyReleased(KeyEvent keyEvent) {
		    	  tu=tuitiontext.getText();
			      System.out.println(tu);
		      }
		      public void keyTyped(KeyEvent keyEvent) {
		      }
		    });
		
		JLabel empty=new JLabel("   ");
		JLabel empty2=new JLabel("   ");
		JLabel empty3=new JLabel("   ");
		JLabel empty4=new JLabel("   ");
		
		n1.add(term);
		n1.add(termtext);
		n1.add(empty);
		n2.add(tuitionRate);
		n2.add(tuitiontext);
		n2.add(empty2);
		
		north.add(empty4);
		north.add(n1);
		north.add(n2);
		north.add(empty3);
		
		panel.add(north,BorderLayout.NORTH);
		
		//center
		JPanel center=new JPanel();
		center.setOpaque(false);
		center.setLayout(new GridLayout(2,1));
		JPanel c1=new JPanel();
		c1.setOpaque(false);
		JPanel c2=new JPanel();
		c2.setOpaque(false);
		
			
		//courseT.setRowSelectionAllowed(true);
        //courseT.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);
		c1.setBorder(BorderFactory.createTitledBorder("Course fee management:"));
		int v = ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS; 
		int h = ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED;
		courseT.setPreferredScrollableViewportSize(new Dimension(580, 70));
		JScrollPane j = new JScrollPane(courseT,v,h);
		courseT.setFillsViewportHeight(true);
		
		courseT.getModel().addTableModelListener(new TableModelListener() {

		      public void tableChanged(TableModelEvent e) {
		         // your code goes here;
		    	  //courseT.fireTableDataChanged();
		    	  //dtm.fireTableChanged(e);
		    	  int row = e.getFirstRow();
		          int column = e.getColumn();
		          TableModel model = (TableModel)e.getSource();
		          String columnName = model.getColumnName(column);
		          Object data = model.getValueAt(row, column);
		    	  System.out.println("changed?"+columnName+" "+row+" "+column+" "+data);
		    	  cb[row][column]=true;
		      }
		    });
				
		TableColumn column;
		column = courseT.getColumnModel().getColumn(0);
		column.setPreferredWidth(100);
		column = courseT.getColumnModel().getColumn(1);
		column.setPreferredWidth(200);
		c1.add(courseT.getTableHeader(), BorderLayout.PAGE_START);
		c1.add(j, BorderLayout.CENTER);
		
		
		
		c2.setBorder(BorderFactory.createTitledBorder("Financial aid management:"));
		studentT.setPreferredScrollableViewportSize(new Dimension(580, 70));
		JScrollPane j2 = new JScrollPane(studentT,v,h);
		studentT.setFillsViewportHeight(true);
		
		studentT.getModel().addTableModelListener(new TableModelListener() {

		      public void tableChanged(TableModelEvent e) {
		         // your code goes here;
		    	  //courseT.fireTableDataChanged();
		    	  //dtm.fireTableChanged(e);
		    	  int row = e.getFirstRow();
		          int column = e.getColumn();
		          TableModel model = (TableModel)e.getSource();
		          String columnName = model.getColumnName(column);
		          Object data = model.getValueAt(row, column);
		    	  System.out.println("changed?"+columnName+" "+row+" "+column+" "+data);
		    	  sb[row][column]=true;
		      }
		    });
			
		c2.add(studentT.getTableHeader(), BorderLayout.PAGE_START);
		c2.add(j2, BorderLayout.CENTER);
		
		center.add(c1);
		center.add(c2);
		
		panel.add(center,BorderLayout.CENTER);
		
		//south
		JPanel south=new JPanel();
		south.setOpaque(false);
		south.setLayout(new GridBagLayout());
		JPanel s=new JPanel();
		s.setOpaque(false);
		
		final JButton submit=new JButton();
		final JButton home=new JButton();
		
		
		s.add(submit);
		s.add(home);
		south.add(s);
		
		panel.add(south,BorderLayout.SOUTH);
		
		  final ImageIcon ho=new ImageIcon("home_button.png");
  		final ImageIcon cho=new ImageIcon("home_cbutton.png");
  		final ImageIcon su=new ImageIcon("submit_button.png");
  		final ImageIcon csu=new ImageIcon("submit_cbutton.png");
  		button(submit,su);
		button(home,ho);
		
		home.addActionListener(new ActionListener(){
	        public void actionPerformed(ActionEvent e){
	            
	            remove(panel);
	            add(new AdminMenuPanel());
	            validate();
	            repaint();
	            
	        }
	});
		 home.setContentAreaFilled(false);
         home.setIcon(ho);
         home.setRolloverIcon(cho);
         home.setPressedIcon(cho);
         home.getModel().addChangeListener(new ChangeListener() {
 		        @Override
 		        public void stateChanged(ChangeEvent e) {
 		            ButtonModel model = (ButtonModel) e.getSource();
 		            if (model.isRollover()) {
 		            	home.setIcon(cho);
 		            } else {
 		            	home.setIcon(ho);
 		            }// etc ...//  if (model.isPressed()) {
 		         }
 		    });
		
		submit.addActionListener(new ActionListener(){
	        public void actionPerformed(ActionEvent e){
	        	 int c = crow;//data.length;
	                for(int i =0; i<c; i++){
	                        String s=(String) courseT.getModel().getValueAt(i, 2);
	                        String f=(String) courseT.getModel().getValueAt(i, 3);
	                        String num=(String) courseT.getModel().getValueAt(i, 0);
	                        //System.out.println(b);
	                        for(int j=0;j<3;j++){
	                        if(cb[i][j]==true)
	                                {
	                                        //System.out.println(s);
	                                        updateCourse(s,f,num);
	                                }
	                        }
	                }
	                
	                int st = srow;//data.length;
	                for(int i =0; i<st; i++){
	                		//float f=(float) studentT.getModel().getValueAt(i, 3);
	                        String s=(String) studentT.getModel().getValueAt(i, 2);
	                        String f=(String) studentT.getModel().getValueAt(i, 3);
	                        String id=(String) studentT.getModel().getValueAt(i, 0);
	                        //System.out.println(b);
	                        for(int j=0;j<3;j++){
	                        if(sb[i][j]==true)
	                                {
	                                        System.out.println(s+" "+f);
	                                        updateStudent(s,f,id);
	                                }
	                        }
	                }
	        	//System.out.println("update");
	            update();
	            System.out.println("updatated");
	            validate();
	            repaint();
	            
	        }
	});
		 submit.setContentAreaFilled(false);
		 submit.setIcon(su);
		 submit.setRolloverIcon(csu);
		 submit.setPressedIcon(csu);
		 submit.getModel().addChangeListener(new ChangeListener() {
 		        @Override
 		        public void stateChanged(ChangeEvent e) {
 		            ButtonModel model = (ButtonModel) e.getSource();
 		            if (model.isRollover()) {
 		            	submit.setIcon(csu);
 		            } else {
 		            	submit.setIcon(su);
 		            }// etc ...//  if (model.isPressed()) {
 		         }
 		    });
		
		setLayout(new BorderLayout());
		//add(backPanel,BorderLayout.PAGE_START);
		add(panel,BorderLayout.PAGE_START);
	}
	
	private void initialize(){
		//course table
		DefaultTableModel dtm=new DefaultTableModel(courseDatas, courseCols);
		courseT=new JTable(dtm);
		String cname,cnum,cid;
		
		try{
			String dName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
			Connection con = DriverManager.getConnection(url);
			java.sql.Statement mysqlStatement = con.createStatement();
			java.sql.ResultSet resultSet = mysqlStatement.executeQuery("SELECT course_name, course_number FROM course_catalog JOIN next_semester_course_list ON course_catalog.CID=next_semester_course_list.CID GROUP BY course_number");
			
			while (resultSet.next()) {
				crow+=1;
				cname=resultSet.getString(1);
				cnum=resultSet.getString(2);
				//cid=resultSet.getString(3);
				String description="", amount="";
				//System.out.println("first");
				
				try{
					String dName2 = "com.mysql.jdbc.Driver";
					String url2 = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
					Connection con2 = DriverManager.getConnection(url2);
					java.sql.Statement mysqlStatement2 = con2.createStatement();
					java.sql.ResultSet resultSet2 = mysqlStatement2.executeQuery("SELECT description, amount FROM next_semester_course_fee_list WHERE CID IN (SELECT CID FROM course_catalog WHERE course_number='"+cnum+"')");
					
					while (resultSet2.next()) {
						//System.out.println("second");
						description=resultSet2.getString(1);
						amount=resultSet2.getString(2);
						pid+=1;
					}
					
					con2.close();
					}
					catch(Exception ex){}
//				
				
				Object[] course={cnum,cname,description,amount};
				dtm.addRow(course);
			}
			cb=new boolean[crow][4];
			
			con.close();
			}
			catch(Exception ex){}
		
		//student table
		DefaultTableModel dtm2=new DefaultTableModel(studentDatas, studentCols);
		studentT=new JTable(dtm2);	
		String SID=MenuPanel.SID;
		String ssid="",sid,grade,aname="",amt="";
		int credits;
		int totalg=1;
		float gpa=0, totalc = 1;
		try{
			String dName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
			Connection con = DriverManager.getConnection(url);
			java.sql.Statement mysqlStatement = con.createStatement();
			java.sql.ResultSet resultSet = mysqlStatement.executeQuery("SELECT student_id, SID FROM student_list AS s WHERE SID!=-1");
			while (resultSet.next()) {
			srow+=1;
			sid= resultSet.getString(1);
			ssid=resultSet.getString(2);
			gpa=0;
			//get gpa
			try{
				String dName2 = "com.mysql.jdbc.Driver";
				String url2 = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
				Connection con2 = DriverManager.getConnection(url);
				java.sql.Statement mysqlStatement2 = con.createStatement();
                java.sql.ResultSet resultSet2 = mysqlStatement2.executeQuery("SELECT grade FROM total_course_list WHERE SID='"+ssid+"'");
                java.sql.Statement mysqlStatement3 = con.createStatement();
                java.sql.ResultSet resultSet3 = mysqlStatement3.executeQuery("SELECT credit_hours  FROM course_catalog WHERE CID IN (SELECT CID FROM total_course_list WHERE SID='"+ssid+"')");
                //java.sql.ResultSet resultSet = mysqlStatement.executeQuery("SELECT major, grade, credit_hours FROM course_catalog, student_list, total_course_list WHERE student_list.SID='"+MenuPanel.SID+"'");// CID IN (SELECT CID FROM total_course_list WHERE SID='"+MenuPanel.SID+"')");
                while (resultSet2.next()&&resultSet3.next()) {
                grade= resultSet2.getString(1);
               // repeats=resultSet2.getInt(2);
                //term=resultSet2.getString(3);
                credits=resultSet3.getInt(1);
               // sectionAndTitle=resultSet3.getString(2)+" - "+resultSet3.getString(3);
               
               
                totalc+=credits;
                if(grade.equals("A")){
                        totalg+=4*credits;
                }
                else if(grade.equals("B")){
                        totalg+=3*credits;
                }
                else if(grade.equals("C")){
                        totalg+=2*credits;
                }
                else if(grade.equals("D")){
                        totalg+=1*credits;
                }
				
				System.out.println(totalg/totalc);
				gpa=totalg/totalc;
				System.out.println("GPA: "+gpa);
				}
				con2.close();
				}
				catch(Exception ex){}
			aname="";
			amt="";
			try{
				String dName2 = "com.mysql.jdbc.Driver";
				String url2 = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
				Connection con2 = DriverManager.getConnection(url2);
				java.sql.Statement mysqlStatement2 = con2.createStatement();
				java.sql.ResultSet resultSet2 = mysqlStatement2.executeQuery("SELECT award_name, amount FROM next_semester_financial_aid_list WHERE SID ='"+ssid+"'");
				
				while (resultSet2.next()) {//need to fix it
					//System.out.println("second");
					aname=resultSet2.getString(1);
					amt=resultSet2.getString(2);
					spid+=1;
				}
				
				con2.close();
				}
				catch(Exception ex){}
			
			System.out.println(ssid);
			
			Object[] student={sid,gpa,aname,amt};
			dtm2.addRow(student);
			
			sb=new boolean[srow][4];
			}
			con.close();
			}
			catch(Exception ex){}
		
		//term, tuition rate
		try{
			String dName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
			Connection con = DriverManager.getConnection(url);
			java.sql.Statement mysqlStatement = con.createStatement();
			java.sql.ResultSet resultSet = mysqlStatement.executeQuery("SELECT next_term, next_tuition_rate FROM admin_finance WHERE AFID=1");
			
			while (resultSet.next()) {
				String term=resultSet.getString(1);
				float tr=resultSet.getFloat(2);
				
				te=term;
				tu=tr+"";
				termtext.setText(te);
				tuitiontext.setText(tu);
				System.out.println("hello?");
				
				
			}
			con.close();
			}
			catch(Exception ex){}
		
		
	}
	
	
	private void update(){
		try{
			String dName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
			Connection con = DriverManager.getConnection(url);
			java.sql.Statement mysqlStatement = con.createStatement();
			mysqlStatement.executeUpdate("UPDATE admin_finance SET next_term='"+te+"', next_tuition_rate ='"+tu+"' WHERE AFID = 1");
			//System.out.println("whyyy");
			
			con.close();
			}
			catch(Exception ex){}
		System.out.println("updated :"+te+" "+tu);
	}
	
	private void updateCourse(String s, String f,String cnum){
		System.out.println("update course: "+s+" "+f+" "+cnum);
		float ff=0;
		if(s.equals(null)||s.equals("")||f.equals(null)||f.equals("")){
			try{
				String dName = "com.mysql.jdbc.Driver";
				String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
				Connection con = DriverManager.getConnection(url);
				java.sql.Statement mysqlStatement = con.createStatement();
				mysqlStatement.executeUpdate("DELETE FROM next_semester_course_fee_list WHERE CID IN (SELECT CID FROM course_catalog WHERE course_number='"+cnum+"')");
				System.out.println(cnum+" "+"dropped");
				con.close();
				}
				catch(Exception ex){}
		}
		else{
			ff=Float.parseFloat(f);
		}
		pid+=1;
		//check if the cid already exsits
		try{
			String dName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
			Connection con = DriverManager.getConnection(url);
			java.sql.Statement mysqlStatement = con.createStatement();
			java.sql.ResultSet resultSet = mysqlStatement.executeQuery("SELECT COUNT(*) FROM next_semester_course_fee_list WHERE CID IN (SELECT CID FROM course_catalog WHERE course_number='"+cnum+"')");
			
			while (resultSet.next()) {
				int ocid=resultSet.getInt(1);
				
				System.out.println("cid already exist?");
				if(ocid!=0){
					
					try{
						String dName2 = "com.mysql.jdbc.Driver";
						String url2 = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
						Connection con2 = DriverManager.getConnection(url2);
						java.sql.Statement mysqlStatement2 = con2.createStatement();
						mysqlStatement2.executeUpdate("UPDATE next_semester_course_fee_list SET description='"+s+"', amount ='"+ff+"' WHERE CID IN (SELECT CID FROM course_catalog WHERE course_number='"+cnum+"')");
						System.out.println("course updated");
						
						con2.close();
						}
						catch(Exception ex){}
				}
				else{
					try{
						String dName2 = "com.mysql.jdbc.Driver";
						String url2 = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
						Connection con2 = DriverManager.getConnection(url2);
						java.sql.Statement mysqlStatement2 = con2.createStatement();
						mysqlStatement2.executeUpdate("INSERT INTO next_semester_course_fee_list VALUES ((SELECT CID FROM course_catalog WHERE course_number='"+cnum+"'),'"+s+"','"+ff+"','"+pid+"')");
						System.out.println("course inserted");
						
						con2.close();
						}
						catch(Exception ex){}
				}
				
			}
			con.close();
			}
			catch(Exception ex){}
		
		
		
	}
	
	private void updateStudent(String s, String f,String id){
		System.out.println("update student: "+s+" "+f+" "+id);
		float ff=0;
		if(s.equals(null)||s.equals("")||f.equals(null)||f.equals("")){
			try{
				String dName = "com.mysql.jdbc.Driver";
				String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
				Connection con = DriverManager.getConnection(url);
				java.sql.Statement mysqlStatement = con.createStatement();
				mysqlStatement.executeUpdate("DELETE FROM next_semester_financial_aid_list WHERE SID IN (SELECT SID FROM student_list WHERE student_id='"+id+"')");
				System.out.println(id+" "+"dropped");
				con.close();
				}
				catch(Exception ex){}
		}
		else{
			ff=Float.parseFloat(f);
		}
		spid+=1;
		//check if the cid already exsits
		try{
			String dName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
			Connection con = DriverManager.getConnection(url);
			java.sql.Statement mysqlStatement = con.createStatement();
			java.sql.ResultSet resultSet = mysqlStatement.executeQuery("SELECT COUNT(*) FROM next_semester_financial_aid_list WHERE SID IN (SELECT SID FROM student_list WHERE student_id='"+id+"')");
			
			while (resultSet.next()) {
				int ocid=resultSet.getInt(1);
				
				System.out.println("sid already exist?");
				if(ocid!=0){
					
					try{
						String dName2 = "com.mysql.jdbc.Driver";
						String url2 = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
						Connection con2 = DriverManager.getConnection(url2);
						java.sql.Statement mysqlStatement2 = con2.createStatement();
						mysqlStatement2.executeUpdate("UPDATE next_semester_financial_aid_list SET award_name='"+s+"', amount ='"+ff+"' WHERE SID IN (SELECT SID FROM student_list WHERE student_id='"+id+"')");
						System.out.println(s+" "+f);
						System.out.println("financial aid updated");
						
						con2.close();
						}
						catch(Exception ex){}
				}
				else{
					try{
						String dName2 = "com.mysql.jdbc.Driver";
						String url2 = "jdbc:mysql://db4free.net:3306/studentreg14?user=studentreg14&password=KCTSkcts";
						Connection con2 = DriverManager.getConnection(url2);
						java.sql.Statement mysqlStatement2 = con2.createStatement();
						mysqlStatement2.executeUpdate("INSERT INTO next_semester_financial_aid_list VALUES ((SELECT SID FROM student_list WHERE student_id='"+id+"'),'"+s+"','"+ff+"','"+te+"','"+spid+"')");
						System.out.println(s+" "+f);
						System.out.println("financial aid inserted");
						
						con2.close();
						}
						catch(Exception ex){}
				}
				
			}
			con.close();
			}
			catch(Exception ex){}
		
		
		
	}
	
	@Override
    public void paintComponent(Graphics g) {
		super.paintComponents(g);
        g.drawImage(img, 0, 0, this);
    }
	
	private void button(JButton b, ImageIcon i){
		b.setBorderPainted(false);
		b.setBorder(null);
		//button.setFocusable(false);
		b.setMargin(new Insets(0, 0, 0, 0));
		b.setContentAreaFilled(false);
		b.setIcon(i);
		//b.setRolloverIcon(myIcon2);
		//b.setPressedIcon(myIcon3);
	}
}
